package com.mp.approvalreq.dao.impl;

import com.alibaba.fastjson.JSONObject;
import com.mp.approvalreq.dao.TransferDao;
import com.mp.approvalreq.entity.*;
import com.mp.approvalreq.entity.po.ComponentGroup;
import com.plusmoney.util.DateTimeHelper;
import com.plusmoney.util.SQLLogger;
import com.plusmoney.util.Utils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;
import java.util.stream.IntStream;
import java.util.stream.Stream;

public class TransferDaoImpl extends AbstractDao implements TransferDao {

    private static final Logger logger = LoggerFactory.getLogger(TransferDaoImpl.class);

    private Connection connection;

    public TransferDaoImpl(Connection connection) {
        this.connection = connection;
    }

    private PreparedStatement getPreparedStatement(String sql) throws SQLException {
        return connection.prepareStatement(sql);
    }

    @Override
    public List<Leave> selectAllLeaveRequestFlow(Integer companyId) throws SQLException {
        List<Leave> list = new ArrayList<>();
        String sql = "select Frequest_id, Fcompany_id, Fapplicant_ciid, Ffinished, Fmanual_ending_time,"
                + " Frequest_content_last_total_second, Fnatural_content_time_last, Fapplicant, Fall_judger, Fwho_confirm"
                + " from request_flow"
                + " where Frequest_template_type in(2,8) and Fcompany_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Leave leave = new Leave();
                    leave.setRequestId(rs.getInt(1));
                    leave.setCompanyId(rs.getInt(2));
                    leave.setCompanyInfoId(rs.getInt(3));
                    int finished = rs.getInt(4);
                    if (finished >= 0 && finished <= 2) {
                        leave.setState(1);
                    } else {
                        leave.setState(2);
                    }
                    String manualEndTime = DateTimeHelper.format(rs.getTimestamp(5), DateTimeHelper.YEAR_MONTH_DAY_HOUR_MINUTE_PATTERN);
                    leave.setEndTime(manualEndTime);
                    leave.setDuration(rs.getInt(6));
                    leave.setShowTime(rs.getString(7));
                    leave.setContactId(rs.getInt(8));

                    String allJudger = rs.getString(9);
                    String whoConfirm = rs.getString(10);

                    if (finished == -1 && StringUtils.isNotBlank(whoConfirm)) {
                        // 判断请假审批在请假开始时间之前就销假的情况
                        String[] judgers = allJudger.split(",");
                        String[] whoConfirms = whoConfirm.split(",");
                        if (judgers.length == whoConfirms.length) {
                            leave.setIsLeavePause(-1);
                        }
                    } else {
                        if (Utils.isNotNull(manualEndTime)) {
                            leave.setIsLeavePause(1);
                        }
                    }


                    list.add(leave);
                }
            }
        }
        return list;
    }


    @Override
    public Map<Integer, List<TemplateComponent>> selectAllComponentByRequestIds(String requestIds) throws SQLException {
        Map<Integer, List<TemplateComponent>> map = new HashMap<>();
        String sql = "select Frequest_id, Fcomponent_num, Fcontent, Fcontent_type"
                + " from request_content"
                + " where Frequest_id in(" + requestIds + ")";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            logger.info(sql);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    TemplateComponent component = new TemplateComponent();
                    int requestId = rs.getInt(1);
                    component.setNum(rs.getInt(2));
                    component.setValue(rs.getString(3));
                    component.setType(rs.getInt(4));
                    if (!map.containsKey(requestId)) {
                        map.put(requestId, new ArrayList<>());
                    }
                    map.get(requestId).add(component);
                }
            }
        }
        return map;
    }

    @Override
    public Map<Integer, String> selectAllLeaveTime(String requestIds) throws SQLException {
        Map<Integer, String> map = new HashMap<>();
        String sql = "select request_id, leave_duration"
                + " from request_time"
                + " where request_id in(" + requestIds + ") and leave_duration is not null";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            logger.info(sql);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    map.put(rs.getInt(1), rs.getString(2));
                }
            }
        }
        return map;
    }

    @Override
    public void insertBatchLeaves(List<Leave> leaves) throws SQLException {
        String sql = "insert into sys_approval_leave set"
                + " company_id = ?, company_info_id = ?, request_id = ?, end_date = ?, end_date = ?,"
                + " real_start_time = ?, real_end_time = ?, leave_type = ?, reason = ?, duration = ?,"
                + " show_time = ?, leave_time_unit = ?, leave_calculate_type = ?, leave_time_detail = ?, state = ?,"
                + " is_leave_pause = ?, create_time = ?, balance_type = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Leave leave : leaves) {
                ps.setInt(1, leave.getCompanyId());
                ps.setInt(2, leave.getCompanyInfoId());
                ps.setInt(3, leave.getRequestId());
                ps.setString(4, leave.getStartDate());
                ps.setString(5, leave.getEndDate());

                Timestamp realStartTime = new Timestamp(leave.getRealStartTime().getTime());
                Timestamp realEndTime = new Timestamp(leave.getRealEndTime().getTime());
                ps.setTimestamp(6, realStartTime);
                ps.setTimestamp(7, realEndTime);
                ps.setString(8, leave.getLeaveType());
                ps.setString(9, leave.getReason());
                ps.setInt(10, leave.getDuration());

                ps.setString(11, leave.getShowTime());
                ps.setInt(12, leave.getLeaveTimeUnit());
                ps.setInt(13, leave.getLeaveCalculateType());
                ps.setString(14, leave.getLeaveTimeDetail());
                ps.setInt(15, leave.getState());

                ps.setInt(16, leave.getIsLeavePause());
                ps.setString(17, leave.getCreateTime());
                ps.setInt(18, leave.getBalanceType());
//				logger.info(SQLLogger.logSQL(sql, leave.getCompanyId(), leave.getCompanyInfoId(), leave.getRequestId(),
//						leave.getStartTime(), leave.getEndTime(), realStartTime, realEndTime,
//						leave.getLeaveType(), leave.getReason(), leave.getDuration(), leave.getShowTime(),
//						leave.getLeaveTimeUnit(), leave.getLeaveCalculateType(), leave.getLeaveTimeDetail(), leave.getState(),
//						leave.getIsLeavePause()));

                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public List<Request> selectAllFlows(Integer companyId) throws SQLException {
        List<Request> list = new ArrayList<>();
        String sql = "select Frequest_id, Fcompany_id, Fall_judger_ciid, Fwho_confirm_ciid, Fwho_the_next_ciid,"
                + " Fwho_refused_ciid, Frequest_step, Ffinished, Fcreate_time, Fchecked_time,"
                + " Fcall_back_time, Fdisable_time, Ffinally_confirmed_time, Fall_judger, Frela_people_ciid"
                + " from request_flow where Fcompany_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Request request = new Request();
                    request.setRequestDkey(rs.getInt(1));
                    request.setCompanyId(rs.getInt(2));
                    request.setAllJudgerCiid(rs.getString(3));
                    request.setWhoConfirmCiid(rs.getString(4));
                    request.setWhoTheNextCiid(rs.getString(5));

                    request.setWhoRefusedCiid(rs.getString(6));
                    request.setRequestStep(rs.getInt(7));
                    request.setFinished(rs.getInt(8));
                    request.setCreateTime(rs.getString(9));
                    request.setCheckedTime(rs.getString(10));

                    request.setCallBackTime(rs.getString(11));
                    request.setDisableTime(rs.getString(12));
                    request.setFinallyConfirmedTime(rs.getString(13));
                    request.setAllJudger(rs.getString(14));
                    request.setRelaPeopleCiid(rs.getString(15));
                    list.add(request);
                }
            }
        }
        return list;
    }

    @Override
    public List<Integer> selectExistFlowRequestIds(Integer companyId) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select distinct(request_id) from sys_approval_flow where company_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public void insertFlows(List<Flow> flows) throws SQLException {
        String sql = "insert into sys_approval_flow set"
                + " request_id = ?, company_id = ?, company_info_id = ?, contact_id = ?, ordering = ?,"
                + " type = ?, is_approved = ?, is_approved_at = ?, state = ?, create_time = now()";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Flow flow : flows) {
                ps.setInt(1, flow.getRequestId());
                ps.setInt(2, flow.getCompanyId());
                ps.setInt(3, flow.getCompanyInfoId());
                ps.setInt(4, flow.getContactId());
                ps.setInt(5, flow.getOrdering());

                ps.setInt(6, flow.getType());
                ps.setInt(7, flow.getIsApproved());
                ps.setString(8, flow.getIsApprovedAt());
                ps.setInt(9, flow.getState());

                logger.info(SQLLogger.logSQL(sql, flow.getRequestId(), flow.getCompanyId(), flow.getCompanyInfoId(),
                        flow.getContactId(), flow.getOrdering(), flow.getType(), flow.getIsApproved(),
                        flow.getIsApprovedAt(), flow.getState()));
                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public void insertBatchParticipants(List<Participant> participants) throws SQLException {
        String sql = "insert into sys_approval_participant"
                + " set company_id = ?, request_id = ?, company_info_id =?, state = ?, create_time = now()";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Participant participant : participants) {
                ps.setInt(1, participant.getCompanyId());
                ps.setInt(2, participant.getRequestId());
                ps.setInt(3, participant.getCompanyInfoId());
                ps.setInt(4, participant.getState());
                logger.info(SQLLogger.logSQL(sql, participant.getCompanyId(), participant.getRequestId(),
                        participant.getCompanyInfoId(), participant.getState()));

                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public List<Outdoor> selectAllOutdoorRequestFlow(Integer companyId) throws SQLException {
        List<Outdoor> list = new ArrayList<>();
        String sql = "select Frequest_id, Fcompany_id, Fapplicant_ciid, Ffinished, Frequest_templet,"
                + " Fouter_position"
                + " from request_flow"
                + " where Frequest_template_type in(3,7) and Fcompany_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Outdoor outdoor = new Outdoor();
                    outdoor.setRequestId(rs.getInt(1));
                    outdoor.setCompanyId(rs.getInt(2));
                    outdoor.setCompanyInfoId(rs.getInt(3));
                    outdoor.setFinished(rs.getInt(4));
                    outdoor.setTemplateId(rs.getInt(5));

                    outdoor.setDestCoordinate(rs.getString(6));
                    list.add(outdoor);
                }
            }
        }
        return list;
    }

    @Override
    public Map<Integer, Template> selectAllTemplateMap(Integer companyId) throws SQLException {
        Map<Integer, Template> map = new HashMap<>();
        String sql = "select Ftemplate_id, Ftemplate_default_type, Ftemplate_component, Fout_sign_offset"
                + " from request_template"
                + " where Fcompany_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Template template = new Template();
                    template.setTemplateId(rs.getInt(1));
                    template.setDefaultType(rs.getInt(2));
                    template.setTemplateComponent(rs.getString(3));
                    template.setOutSignOffset(rs.getString(4));
                    map.put(template.getTemplateId(), template);
                }
            }
        }
        return map;
    }

    @Override
    public void insertBatchOutdoors(List<Outdoor> outdoors) throws SQLException {
        String sql = "insert into sys_approval_outdoor set"
                + " company_id = ?, company_info_id = ?, request_id = ?, start_time = ?, end_time = ?,"
                + " src_address = ?, src_coordinate = ?, dest_address = ?, dest_coordinate = ?, sign_offset = ?,"
                + " reason = ?, transportation = ?, stay_day = ?, attach_file = ?, type = ?,"
                + " state = ?, create_time = now()";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Outdoor outdoor : outdoors) {
                ps.setInt(1, outdoor.getCompanyId());
                ps.setInt(2, outdoor.getCompanyInfoId());
                ps.setInt(3, outdoor.getRequestId());
                ps.setString(4, outdoor.getStartTime());
                ps.setString(5, outdoor.getEndTime());

                ps.setString(6, outdoor.getSrcAddress());
                ps.setString(7, outdoor.getSrcCoordinate());
                ps.setString(8, outdoor.getDestAddress());
                ps.setString(9, outdoor.getDestCoordinate());
                ps.setInt(10, outdoor.getSignOffset());

                ps.setString(11, outdoor.getReason());
                ps.setString(12, outdoor.getTransportation());
                ps.setString(13, outdoor.getStayDay());
                ps.setString(14, outdoor.getAttachFile());
                ps.setInt(15, outdoor.getType());

                ps.setInt(16, outdoor.getState());

                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public List<Integer> selectExistOutdoorRequestIds(Integer companyId) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select request_id from sys_approval_outdoor where company_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public List<AppendSign> selectAllAppendSignRequestFlow(Integer companyId) throws SQLException {
        List<AppendSign> list = new ArrayList<>();
        String sql = "select Frequest_id, Fcompany_id, Fapplicant_ciid, Ffinished, Frequest_templet"
                + " from request_flow"
                + " where Frequest_template_type = 5 and Fcompany_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    AppendSign appendSign = new AppendSign();
                    appendSign.setRequestId(rs.getInt(1));
                    appendSign.setCompanyId(rs.getInt(2));
                    appendSign.setCompanyInfoId(rs.getInt(3));
                    int finished = rs.getInt(4);
                    if (finished >= 0 && finished <= 2) {
                        appendSign.setState(1);
                    } else {
                        appendSign.setState(2);
                    }
                    appendSign.setTemplateId(rs.getInt(5));

                    list.add(appendSign);
                }
            }
        }
        return list;

    }

    @Override
    public List<Integer> selectAllAppendSignRequestIds(Integer companyId) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select request_id from sys_approval_append_sign where company_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public void insertBatchAppendSign(List<AppendSign> appendSigns) throws SQLException {
        String sql = "insert into sys_approval_append_sign set"
                + " company_id = ?, company_info_id = ?, request_id = ?, date = ?, sign_type = ?,"
                + " sign_time = ?, reason = ?, schedule_name = ?, work_time_range = ?, state = ?, create_time = now()";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (AppendSign appendSign : appendSigns) {
                try {
                    ps.setInt(1, appendSign.getCompanyId());
                    ps.setInt(2, appendSign.getCompanyInfoId());
                    ps.setInt(3, appendSign.getRequestId());
                    ps.setString(4, appendSign.getDate());
                    ps.setInt(5, appendSign.getSignType());

                    ps.setTimestamp(6, new Timestamp(appendSign.getSignTime().getTime()));
                    ps.setString(7, appendSign.getReason());
                    ps.setString(8, appendSign.getScheduleName());
                    ps.setString(9, appendSign.getWorkTimeRange());
                    ps.setInt(10, appendSign.getState());

                    ps.addBatch();
                } catch (Throwable e) {
                    logger.error("插入数据异常:{}", appendSign);
                }
            }
            ps.executeBatch();
        }
    }

    @Override
    public List<Overtime> selectAllOvertimes(Integer companyId) throws SQLException {
        List<Overtime> list = new ArrayList<>();
        String sql = "select Frequest_id, Fcompany_id, Fapplicant_ciid, Ffinished, Frequest_templet,"
                + " Fouter_position, Fcreate_time"
                + " from request_flow"
                + " where Frequest_template_type = 9 and Fcompany_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Overtime overtime = new Overtime();
                    overtime.setRequestId(rs.getInt(1));
                    overtime.setCompanyId(rs.getInt(2));
                    overtime.setCompanyInfoId(rs.getInt(3));
                    int finished = rs.getInt(4);
                    if (finished < 0 || finished > 2) {
                        overtime.setState(2);
                    } else {
                        overtime.setState(1);
                    }
                    overtime.setTemplateId(rs.getInt(5));

                    overtime.setCoordinate(rs.getString(6));
                    overtime.setCreateTime(getTimeStr(rs.getTimestamp(7)));
                    list.add(overtime);
                }
            }
        }
        return list;
    }

    @Override
    public List<Integer> selectExistOvertimeRequestIds(Integer companyId) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select request_id from sys_approval_overtime where company_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public void insertBatchOvertimes(List<Overtime> overtimes) throws SQLException {
        String sql = "insert into sys_approval_overtime set"
                + " company_id = ?, company_info_id = ?, request_id = ?, start_time = ?, end_time = ?,"
                + " duration = ?, show_time = ?, address = ?, coordinate = ?, reason = ?,"
                + " state = ?, create_time = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Overtime overtime : overtimes) {
                ps.setInt(1, overtime.getCompanyId());
                ps.setInt(2, overtime.getCompanyInfoId());
                ps.setInt(3, overtime.getRequestId());
                ps.setString(4, overtime.getStartTime());
                ps.setString(5, overtime.getEndTime());

                ps.setInt(6, overtime.getDuration());
                ps.setString(7, overtime.getShowTime());
                ps.setString(8, overtime.getAddress());
                ps.setString(9, overtime.getCoordinate());
                ps.setString(10, overtime.getReason());
                ps.setInt(11, overtime.getState());
                ps.setString(12, overtime.getCreateTime());
//				logger.info(SQLLogger.logSQL(sql, overtime.getCompanyId(), overtime.getCompanyInfoId(), overtime.getRequestId(), overtime.getStartTime(),
//						overtime.getEndTime(), overtime.getDuration(), overtime.getShowTime(), overtime.getAddress(), overtime.getCoordinate(),
//						overtime.getReason(), overtime.getState(),overtime.getCreateTime()));

                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public List<Integer> selectExistLeaveRequestIds(Integer companyId) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select request_id from sys_approval_leave where company_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public void clearReadPoint() throws SQLException {
        String sql = "update request_message_read set Fis_read = 1, Fread_time = now()"
                + " where Frequest_id in(select Frequest_id from request_flow where Frequest_templet in(select Ftemplate_id from request_template where Fancestor_id = 4176)) and Fis_read = 0";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            logger.info(sql);

            ps.executeUpdate();
        }
    }

    @Override
    public List<Cc> selectAllCcTemp(String requestIds) throws SQLException {
        List<Cc> list = new ArrayList<>();
        if (StringUtils.isEmpty(requestIds)) {
            return list;
        }
        String sql = "select Frequest_id, Fcc, Fadder from pt_mp_approval_ccandadder_relationships where Fstatus = 0 and Frequest_id in(" + requestIds + ")";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            logger.info(sql);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Cc cc = new Cc();
                    cc.setRequestId(rs.getInt(1));
                    cc.setCcId(rs.getInt(2));
                    cc.setCreatorContactId(rs.getInt(3));
                    list.add(cc);
                }
            }
        }
        return list;
    }

    @Override
    public List<Cc> selectAllCc(Integer companyId) throws SQLException {
        List<Cc> list = new ArrayList<>();
        String sql = "select Fid, Frequest_id, Fwho_filed_ciid, Fcompany_id, Ffiled_time,"
                + " Ffiled_status"
                + " from request_filed"
                + " where Fcompany_id = ? and Fstatus = 0";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Cc cc = new Cc();
                    cc.setId(rs.getInt(1));
                    cc.setRequestId(rs.getInt(2));
                    cc.setCcCiid(rs.getInt(3));
                    cc.setCompanyId(rs.getInt(4));
                    cc.setCcTime(DateTimeHelper.format(rs.getTimestamp(5), DateTimeHelper.DEFAULT_PATTERN));
                    cc.setFiledStatus(rs.getInt(6));
                    list.add(cc);
                }
            }
        }
        return list;
    }

    @Override
    public void updateCc(List<Cc> ccList) throws SQLException {
        String sql = "update request_filed set Fis_temp = 1, Fcrt_by = ? where Fid = ? and Fcompany_id = ? limit 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Cc cc : ccList) {
                ps.setInt(1, cc.getCreatorId());
                ps.setInt(2, cc.getId());
                ps.setInt(3, cc.getCompanyId());
                logger.info(SQLLogger.logSQL(sql, cc.getCreatorId(), cc.getId(), cc.getCompanyId()));

                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public void clearCcTime(Integer companyId) throws SQLException {
        String sql = "update request_filed set Ffiled_time = null where Fcompany_id = ? and Ffiled_status != 2";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            ps.executeUpdate();
        }
    }

    @Override
    public List<TemplateComponent> selectAllGroupContent() throws SQLException {
        List<TemplateComponent> list = new ArrayList<>();
        String sql = "select Ftemplate_id, Fcomponent_num, Fcontent, Frequest_id, Fcontent_type, Fsort"
                + " from request_content"
                + " where Fcontent_type = 24 and Frequest_id in(1873184,1873250)";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            logger.info(sql);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    TemplateComponent component = new TemplateComponent();
                    component.setTemplateId(rs.getInt(1));
                    component.setNum(rs.getInt(2));
                    component.setValue(rs.getString(3));
                    component.setRequestId(rs.getInt(4));
                    component.setType(rs.getInt(5));
                    list.add(component);
                }
            }
        }
        return list;
    }

    @Override
    public Map<Integer, Template> selectTemplates(String templateIds) throws SQLException {
        Map<Integer, Template> map = new HashMap<>();
        String sql = "select Ftemplate_id, Fcompany_id, Fcomponent_group_json"
                + " from request_template"
                + " where Ftemplate_id in(" + templateIds + ")";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            logger.info(SQLLogger.logSQL(sql));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Template template = new Template();
                    template.setTemplateId(rs.getInt(1));
                    template.setCompanyId(rs.getInt(2));
                    template.setComponentGroupJson(rs.getString(3));

                    map.put(template.getTemplateId(), template);
                }
            }
        }
        return map;
    }

    @Override
    public List<Request> selectAllExpenses(Integer companyId) throws SQLException {
        List<Request> list = new ArrayList<>();
        String sql = "select Frequest_id, Fapplicant_ciid, Fcreate_time, Ffinished, Ftotal_money,"
                + " Frequest_templet"
                + " from request_flow"
                + " where Fcompany_id = ? and Frequest_template_type = 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Request request = new Request();
                    request.setRequestDkey(rs.getInt(1));
                    request.setApplicantCiid(rs.getInt(2));
                    request.setCreateTimestamp(rs.getTimestamp(3));
                    request.setFinished(rs.getInt(4));
                    request.setTotalMoney(rs.getString(5));

                    request.setTemplateId(rs.getInt(6));
                    list.add(request);
                }
            }
        }
        return list;
    }

    @Override
    public void insertBatchExpenses(List<Expense> expenses) throws SQLException {
        String sql = "insert into sys_approval_expense set"
                + " company_id = ?, company_info_id = ?, request_id = ?, total_amount = ?, expense_item = ?,"
                + " pay_amount = ?, pay_date = ?, attach_file = ?, remark = ?, type = ?,"
                + " create_time = ?, state = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Expense expense : expenses) {
                ps.setInt(1, expense.getCompanyId());
                ps.setInt(2, expense.getCompanyInfoId());
                ps.setInt(3, expense.getRequestId());
                ps.setBigDecimal(4, expense.getTotalAmount());
                ps.setString(5, expense.getExpenseItem());

                ps.setBigDecimal(6, expense.getPayAmount());
                ps.setString(7, expense.getPayDate());
                String attachFiles = Utils.isEmpty(expense.getAttachFile()) ? null : JSONObject.toJSONString(expense.getAttachFile());
                ps.setString(8, attachFiles);
                ps.setString(9, expense.getRemark());
                ps.setInt(10, expense.getType());

                ps.setString(11, expense.getCreateTime());
                ps.setInt(12, expense.getState());

                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public List<Request> selectAllExpenses2(Integer companyId) throws SQLException {
        List<Request> list = new ArrayList<>();
        String sql = "select Frequest_id, Fapplicant_ciid, Fcreate_time, Ffinished, Frequest_templet"
                + " from request_flow"
                + " where Fcompany_id = ? and Frequest_template_type = 4";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Request request = new Request();
                    request.setRequestDkey(rs.getInt(1));
                    request.setApplicantCiid(rs.getInt(2));
                    request.setCreateTimestamp(rs.getTimestamp(3));
                    request.setFinished(rs.getInt(4));
                    request.setTemplateId(rs.getInt(5));

                    list.add(request);
                }
            }
        }
        return list;
    }

    @Override
    public List<Integer> selectAllRequestIdsByCompanyId(Integer companyId) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select Frequest_id from request_flow where Fcompany_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public void updateComponentCompanyId(Integer companyId, List<Integer> requestIds) throws SQLException {
        if (Utils.isEmpty(requestIds)) {
            return;
        }

        String sql = "update request_content set Fcompany_id = ? where Frequest_id in(" + Utils.list2string(requestIds) + ")";
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            ps.executeUpdate();
        }
    }

    @Override
    public void updateReqPointCompanyId(Integer companyId, List<Integer> requestIds) throws SQLException {
        if (Utils.isEmpty(requestIds)) {
            return;
        }

        String sql = "update request_message_read set Fcompany_id = ? where Frequest_id in(" + Utils.list2string(requestIds) + ")";
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            ps.executeUpdate();
        }
    }

    @Override
    public List<Template> selectAllTemplateByType(Integer companyId) throws SQLException {
        List<Template> list = new ArrayList<>();
        String sql = "select Ftemplate_id, Ftemplate_component, Fmulti_chosen, Fpriority_show, Fcomponent_group_json,"
                + " Fmoney_amount_nums, Frelate_analysis_num, Ftemplate_name"
                + " from request_template"
                + " where Fcompany_id = ? and Ftemplate_default_type = 6";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Template template = new Template();
                    template.setTemplateId(rs.getInt(1));
                    template.setTemplateComponent(rs.getString(2));
                    template.setMultiChosen(rs.getString(3));
                    template.setPriorityShow(rs.getString(4));
                    template.setComponentGroupJson(rs.getString(5));

                    template.setMoneyAmountNums(rs.getString(6));
                    template.setRelateAnalysisNum(rs.getString(7));
                    template.setTemplateName(rs.getString(8));
                    template.setCompanyId(companyId);
                    list.add(template);
                }
            }
        }
        return list;
    }

    @Override
    public void updateBatchTemplate(List<Template> dataToUpdate) throws SQLException {
        String sql = "update request_template"
                + " set Ftemplate_component = ?"
                + " where Ftemplate_id = ? and Fcompany_id = ? limit 1";
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Template template : dataToUpdate) {
                ps.setString(1, template.getTemplateComponent());
                ps.setInt(2, template.getTemplateId());
                ps.setInt(3, template.getCompanyId());

                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public List<Request> selectAllDaysOffRequest(Integer companyId) throws SQLException {
        List<Request> list = new ArrayList<>();
        String sql = "select Frequest_id, Ffinished, Fis_resubmit, Fapplicant_ciid, Fcreate_time, Fapplicant from request_flow where Fcompany_id = ? and Frequest_template_type = 12";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Request request = new Request();
                    request.setRequestDkey(rs.getInt(1));
                    request.setFinished(rs.getInt(2));
                    request.setIsResubmit(rs.getInt(3));
                    request.setApplicantCiid(rs.getInt(4));
                    request.setCreateTime(rs.getString(5));
                    request.setApplicant(rs.getInt(6));
                    list.add(request);
                }
            }
        }
        return list;
    }

    @Override
    public List<Integer> selectExistExpense(Integer companyId) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select distinct(request_id) from sys_approval_expense where company_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public void updateBatchExpenseType() throws SQLException {
        String sql = "update sys_approval_expense set type = 2 where request_id in(select Frequest_id from request_flow where Frequest_template_type =4) limit 18000";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            logger.info(sql);

            ps.executeUpdate();
        }
    }

    @Override
    public Map<Integer, List<TemplateComponent>> selectAllComponents(Integer companyId) throws SQLException {
        Map<Integer, List<TemplateComponent>> map = new HashMap<>();
        String sql = "select Frequest_id, Fcomponent_num, Fcontent, Fcontent_type"
                + " from request_content"
                + " where Fcompany_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    TemplateComponent component = new TemplateComponent();
                    int requestId = rs.getInt(1);
                    component.setNum(rs.getInt(2));
                    component.setValue(rs.getString(3));
                    component.setType(rs.getInt(4));
                    if (!map.containsKey(requestId)) {
                        map.put(requestId, new ArrayList<>());
                    }
                    map.get(requestId).add(component);
                }
            }
        }
        return map;
    }

    @Override
    public List<Request> selectAllRequestByType(Integer companyId) throws SQLException {
        List<Request> list = new ArrayList<>();
        String sql = "select Frequest_id, Fapplicant_ciid, Frequest_templet, Fcreate_time, Ffinished, Fapproval_num"
                + " from request_flow"
                + " where Fcompany_id = ? and Frequest_template_type = 6";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Request request = new Request();
                    request.setRequestDkey(rs.getInt(1));
                    request.setApplicantCiid(rs.getInt(2));
                    request.setTemplateId(rs.getInt(3));
                    request.setCreateTime(getTimeStr(rs.getTimestamp(4)));
                    request.setFinished(rs.getInt(5));
                    request.setApprovalNum(rs.getString(6));
                    list.add(request);
                }
            }
        }
        return list;
    }

    @Override
    public void insertMoneys(List<Money> list) throws SQLException {
        String sql = "insert into sys_approval_money set"
                + " company_id = ?, company_info_id = ?, request_id = ?, template_name = ?, amount = ?,"
                + " num = ?, name = ?, sub_num = ?, sub_name = ?, is_related_to_cost = ?,"
                + " is_relate_to_amoeba = ?, is_sum = ?, create_time = ?, state = ?, approval_num = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Money money : list) {
                ps.setInt(1, money.getCompanyId());
                ps.setInt(2, money.getCompanyInfoId());
                ps.setInt(3, money.getRequestId());
                ps.setString(4, money.getTemplateName());
                ps.setBigDecimal(5, money.getAmount());

                ps.setInt(6, money.getNum());
                ps.setString(7, money.getName());
                ps.setObject(8, money.getSubNum());
                ps.setString(9, money.getSubName());
                ps.setInt(10, money.getIsRelatedToCost());

                ps.setInt(11, money.getIsRelatedToAmoeba());
                ps.setInt(12, money.getIsSum());
                ps.setString(13, money.getCreateTime());
                ps.setInt(14,money.getState());
                ps.setString(15,money.getApprovalNum());

                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public Map<Integer, Map<Integer, List<GroupContent>>> selectGroupComponent(Integer companyId) throws SQLException {
        Map<Integer, Map<Integer, List<GroupContent>>> map = new LinkedHashMap<>();
        String sql = "select request_id, content_num, sort, value from"
                + " sys_approval_component_group_value"
                + " where company_id = ? order by request_id, content_num, sort";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    int requestId = rs.getInt(1);
                    int num = rs.getInt(2);
                    GroupContent groupContent = new GroupContent();
                    groupContent.setSort(rs.getInt(3));
                    groupContent.setValue(rs.getString(4));

                    if (!map.containsKey(requestId)) {
                        map.put(requestId, new LinkedHashMap<>());
                    }
                    Map<Integer, List<GroupContent>> subMap = map.get(requestId);

                    if (!subMap.containsKey(num)) {
                        subMap.put(num, new ArrayList<>());
                    }
                    subMap.get(num).add(groupContent);
                }
            }
        }
        return map;
    }

    @Override
    public List<GroupContent> selectAllGroupContentValue(Integer companyId) throws SQLException {
        List<GroupContent> list = new ArrayList<>();
        String sql = "select id, company_id, component_group_id, value"
                + " from sys_approval_component_group_value"
                + " where company_id = ?";
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    GroupContent groupContent = new GroupContent();
                    groupContent.setId(rs.getInt(1));
                    groupContent.setCompanyId(rs.getInt(2));
                    groupContent.setGroupId(rs.getInt(3));
                    groupContent.setValue(rs.getString(4));
                    list.add(groupContent);
                }
            }
        }
        return list;
    }

    @Override
    public List<ComponentGroup> selectAllGroup(Integer companyId) throws SQLException {
        List<ComponentGroup> list = new ArrayList<>();
        String sql = " select id, company_id, size, name, is_common_group,"
                + " sub_component "
                + " from request_component_group where company_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    ComponentGroup componentGroup = new ComponentGroup();
                    componentGroup.setId(rs.getInt(1));
                    componentGroup.setCompanyId(rs.getInt(2));
                    componentGroup.setSize(rs.getInt(3));
                    componentGroup.setName(rs.getString(4));
                    componentGroup.setIsCommonGroup(rs.getInt(5));

                    componentGroup.setComponents(rs.getString(6));


                    list.add(componentGroup);
                }
            }
        }

        return list;
    }

    @Override
    public void updateGroupContentValue(List<GroupContent> values) throws SQLException {
        String sql = "update sys_approval_component_group_value set value = ? where id = ? and company_id = ? limit 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (GroupContent value : values) {
                ps.setString(1, JSONObject.toJSONString(value.getValue()));
                ps.setInt(2, value.getId());
                ps.setInt(3, value.getCompanyId());

                ps.addBatch();
            }
            int[] nums = ps.executeBatch();
            int sum = IntStream.of(nums).sum();
            logger.info("共:{},更新:{}", values.size(), sum);
        }

    }

    @Override
    String getQuerySql(String condition) {
        return null;
    }

    @Override
    Object getObject(ResultSet rs) throws SQLException {
        return null;
    }
}
